<?php

class Stats_model extends CI_Model {

    /**
     * Constructor
     */
    function __construct() {
        parent::__construct();
    }

    function _songPopuler() {
        $ress = $this->db->query("select a.*, b.kode, b.title, b.artist, b.genre, b.album, count(a.id) as jml  from action_logs a left join m_song b ON(a.id_song = b.id) where a.`action` IN ('sing','watch') group by a.id_song order by jml DESC LIMIT 0,10")->result_array();
        return $ress;
    }
    
    function _songFavorite() {
        $ress = $this->db->query("select a.id_song,  b.kode, b.title, b.artist, b.genre, b.album, avg(a.rate) as jml  from rate_song a left join m_song b ON(a.id_song = b.id)  group by a.id_song order by jml DESC LIMIT 0,10")->result_array();
        return $ress;
    }
    
    function _artistPopuler() {
        $ress = $this->db->query("select a.*, b.kode, b.title, b.artist, b.genre, b.album, count(a.id) as jml  from action_logs a left join m_song b ON(a.id_song = b.id) where a.`action` IN ('sing','watch') group by b.artist order by jml DESC LIMIT 0,10")->result_array();
        return $ress;
    }
    
    function _artistFavorite() {
        $ress = $this->db->query("select a.id_song,  b.kode, b.title, b.artist, b.genre, b.album, avg(a.rate) as jml  from rate_song a left join m_song b ON(a.id_song = b.id)  group by b.artist order by jml DESC LIMIT 0,10")->result_array();
        return $ress;
    }
    
    function _userLogin() {
        $user = array();
        $result = array();
        $user = $this->db->query("select username, count(username) as jml_username from action_logs where `action` = 'login' and YEAR(action_time) = ".date("Y")." group by username order by jml_username DESC LIMIT 0, 15")->result_array();
      
       foreach($user as $row) {
          
          for($i=1;$i<=12;$i++) { 
          $ress = array();
          $ress = $this->db->query("select count(id) as jml from action_logs where `action` = 'login' and YEAR(action_time) = ".date("Y")." and username = '{$row['username']}' and MONTH(action_time) = $i")->row_array();
          $row['login'][$i] = $ress['jml'];          
          }
        $result[] = $row;
       }
        return $result;
    }
    
    function _userKaraoke() {
         $user = array();
        $result = array();
        $user = $this->db->query("select username, count(username) as jml_username from action_logs where `action` = 'sing' and YEAR(action_time) = ".date("Y")." group by username order by jml_username DESC LIMIT 0, 15")->result_array();
      
       foreach($user as $row) {
          
          for($i=1;$i<=12;$i++) { 
          $ress = array();
          $ress = $this->db->query("select count(id) as jml from action_logs where `action` = 'sing' and YEAR(action_time) = ".date("Y")." and username = '{$row['username']}' and MONTH(action_time) = $i")->row_array();
          $row['karaoke'][$i] = $ress['jml'];          
          }
        $result[] = $row;
       }
        return $result;
    }
    
    function _userRecord() {
        $user = array();
        $result = array();
        $user = $this->db->query("select username, count(username) as jml_username from action_logs where `action` = 'record' and YEAR(action_time) = ".date("Y")." group by username order by jml_username DESC LIMIT 0, 15")->result_array();
      
       foreach($user as $row) {
          
          for($i=1;$i<=12;$i++) { 
          $ress = array();
          $ress = $this->db->query("select count(id) as jml from action_logs where `action` = 'record' and YEAR(action_time) = ".date("Y")." and username = '{$row['username']}' and MONTH(action_time) = $i")->row_array();
          $row['record'][$i] = $ress['jml'];          
          }
        $result[] = $row;
       }
        return $result;
    }
    

  
}

